package com.asdan.seededu.front.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONArray;
import com.asdan.seededu.entity.security.School;
import com.asdan.seededu.entity.security.SubProject;
import com.asdan.seededu.entity.security.SubProjectStep;
import com.asdan.seededu.entity.security.User;
import com.asdan.seededu.front.service.Product01Service;
import com.asdan.seededu.service.impl.UtilImpl;
import com.asdan.seededu.util.DatabaseAccess;
import com.demo2do.core.utils.JsonUtils;

public class Product01ServiceImp extends UtilImpl implements Product01Service {
	
	public SubProject getsubpro(int uid,int pid){
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		SubProject subpro=null;
		
 		String sql="select SUBPROJECTNAME,STARTTIME, description ,SPID from v_user_school_project_subproject where uid=? and SPID=?";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, uid) ;
			pstmt.setInt(2, pid) ;
		    rs=pstmt.executeQuery();
			if(rs.next()){
				subpro=new SubProject();
				subpro.setName(rs.getString("SUBPROJECTNAME"));
				subpro.setStartTime(rs.getString("STARTTIME"));
				subpro.setProjectinformation(rs.getString("description"));
				subpro.setSpid(rs.getInt("SPID"));
				
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					
			}
		}

		return subpro;
	}
	
	
	public String getSchoolId(String schoolname){
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String schoolid=null;
		
 		String sql="select schoolid from school where schoolname='"+schoolname+"'";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			//pstmt.setString(1,schoolname);
			rs=pstmt.executeQuery();
			if(rs.next()){
				schoolid=rs.getString("schoolid");
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					
			}
		}
		return schoolid;
		
	}

	public List<School> getSchools() {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		School school=null;
		List list=new ArrayList();
		
 		String sql="select schoolname,schoolid from school";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				school=new School();
				school.setSchoolNAME(rs.getString("schoolname"));
				school.setSchoolId(rs.getInt("schoolid"));
				list.add(school);
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					
			}
		}
		
		return list;
	}
   public List<SubProjectStep> getLiucheng(int pid){
	   DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		SubProjectStep subprostep=null;
		List<SubProjectStep> list=new ArrayList<SubProjectStep>();
		String sql="select * from subprojectstep where  spid=?";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, pid) ;
			 rs=pstmt.executeQuery();
			while(rs.next()){
				subprostep = new SubProjectStep() ;
		        subprostep.setStepid(rs.getInt("stepid"));
		        subprostep.setSpid(rs.getInt("spid"));
		        subprostep.setName(rs.getString("name"));
		        subprostep.setStepName(rs.getString("stepName"));
		        list.add(subprostep);
			}
			
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}	
		}
		return list;
   }
	
	public int getPid(String pname){
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		int spid=0;
		String sql="select spid from subproject where name=?";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setString(1, pname) ;
			 rs=pstmt.executeQuery();
			if(rs.next()){
		    spid=rs.getInt("spid");
			}
			
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}	
		}
		return spid;
	}
	public User getData(int uid,int pid){
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		User user=null;
		String sql="select data from userdata where uid=? and spid=? ";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, uid) ;
			pstmt.setInt(2, pid) ;
			
			 rs=pstmt.executeQuery();
			if(rs.next()){
				user=JsonUtils.parse(rs.getString("data"), User.class);
			}
			
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}	
		}
		return user;
	}
	
	public boolean saveInfo(int stepid ,int spid,int uid,String cname,String ename,String sex, String birthday , String idcard , String passid, String address , String schoolenglishname , int schoolid,String grade,String phone,String qq,String email,String fatherphone,String motherphone,
			String fatheremail,String motheremail) {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		boolean temp=true;
		
		System.out.println(spid+"cccccccccccccvvvvvvvvvvvvvvvvv");
			//System.out.println(stepid+"stepid+++++++++++++++++++++++");
		/*User user=this.getData(uid, spid);
			user.getSchool().setSchoolNAME(cname);
			user.getSchool().setSchoolENGLISHNAME(ename);
			user.setSex(sex);
			user.setAddress(address);
			user.setBirthday(birthday);
			user.setIdCard(idcard);
			user.setPassid(passid);
			user.setName(cname);
			user.setEmail(email);
			user.setGrade(grade);
			user.setCellPhone(phone);
			user.setQQ(qq);
			user.setFatherEmail(fatheremail);
			user.setFatherPhone(fatherphone);
			user.setMotherEmail(motheremail);
			user.setMotherPhone(motherphone);
			String data=JsonUtils.toJsonString(JSONArray.toJSON(user));
			System.out.println(data+"data++++++++++");
			*/
			Map <String,String> hashmap=new HashMap <String,String>();
			hashmap.put("uid", uid+"");
			hashmap.put("cname", cname);
			hashmap.put("ename", ename);
			hashmap.put("sex", sex);
			hashmap.put("birthday", birthday);
			hashmap.put("idcard", idcard);
			hashmap.put("passid", passid);
			hashmap.put("address", address);
			hashmap.put("schoolenglishname", schoolenglishname);
			hashmap.put("schoolid", schoolid+"");
			hashmap.put("grade", grade);
			hashmap.put("phone", phone);
			hashmap.put("qq", qq);
			hashmap.put("email", email);
			hashmap.put("fatherphone", fatherphone);
			hashmap.put("fatheremail", fatheremail);
			hashmap.put("motherphone", motherphone);
			hashmap.put("motheremail", motheremail);
			String data=JsonUtils.toJsonString(JSONArray.toJSON(hashmap));
			//String sql="";
			
	 		try{
	 			
				String hql="update userdata set uid=?, stepid=?, stepstatus=?, data=?, spid=? where uid=? and spid=?";
				cn = databaseAccess.getConn();
				pstmt = cn.prepareStatement(hql);
				pstmt.setInt(1, uid) ;
				pstmt.setInt(2, stepid) ;
				pstmt.setInt(3, 1) ;
				pstmt.setString(4, data) ;
				pstmt.setInt(5, spid);
				pstmt.setInt(6, uid) ;
				pstmt.setInt(7, spid) ;
			  System.out.println(stepid+"sssssssssssss");
	 			pstmt.execute();
				temp=true;
			}catch(Exception e){
				e.printStackTrace();
				System.out.println(e.getMessage());
				temp=false;
			}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
				try{
					pstmt.close();
					cn.close();
					databaseAccess.close() ;
				}catch(Exception ex){
						pstmt = null ;
						cn = null ;
						temp=false;
	
				}
			}
		return temp;
	}

	public boolean payInfo(int stepid,int uid,int spid,String accountholder, String bankname,
			String bankaccountnumber, String transferamount,
			String transferdate, String picture) {
		
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		boolean temp=true;
		System.out.println("accountholder "+accountholder);
		User user=this.getData(uid, spid);
		if(accountholder!=null &&!"".equals(accountholder) &&bankname!=null&&!"".equals(bankname)&& bankaccountnumber!=null &&!"".equals(bankaccountnumber)
				&&transferamount!=null&&!"".equals(transferamount)&&transferdate!=null&&!"".equals(transferdate)&&picture!=null&&!"".equals(picture)){
			
			user.setKhr(accountholder);
			user.setKhyh(bankname);
			user.setYhzh(bankaccountnumber);
			user.setZzje(transferamount);
			user.setZzrq(transferdate);
			user.setZztpURL(picture);
			
		
		String data=JsonUtils.toJsonString(JSONArray.toJSON(user));
		System.out.println(data+"data++++++++++");
		try{
			
				String hql="update userdata set stepid=?, data=?,stepstatus=?  where uid=? and spid=?";
				cn = databaseAccess.getConn();
				pstmt = cn.prepareStatement(hql);
				
				pstmt.setInt(1, stepid) ;
				pstmt.setString(2, data) ;
				pstmt.setInt(3, 1) ;
				pstmt.setInt(4, uid) ;
				pstmt.setInt(5, spid) ;
			    pstmt.execute();
			    System.out.println(stepid+"stepid2+++++++++++++++++++++++");
				temp=true;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			temp=false;
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					temp=false;

			}
		}
		}else {
			temp=false;
		}
		return temp;
	}
	public boolean isExist(int uid,int pid,int stepid){
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		boolean flag=false;
		String sql="select * from userdata where uid=? and spid=? and stepid=?";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, uid) ;
			pstmt.setInt(2, pid) ;
			pstmt.setInt(3, stepid) ;
			 rs=pstmt.executeQuery();
			if(rs.next()){
		       flag=true;
			}
			
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}	
		}
		return flag;
	}

	public boolean xieyiSend(int stepid,int uid, int pid, String expresstype,
			String senttime, String expressnumber, String state) {
		
		
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		boolean temp=true;
		User user=this.getData(uid, pid);
		user.setKdlx(expresstype);
		user.setFjsj(senttime);
		user.setKddh(expressnumber);
		user.setQszt(state);
		String data=JsonUtils.toJsonString(JSONArray.toJSON(user));
		/*
		Map <String,String> hashmap=new HashMap <String,String>();
		hashmap.put("expresstype", expresstype);
		hashmap.put("senttime", senttime);
		hashmap.put("expressnumber", expressnumber);
		hashmap.put("state", state);
		
		String data=JsonUtils.toJsonString(JSONArray.toJSON(hashmap));*/
		System.out.println(data+"data++++++++++");
 		
		try{
			
				cn = databaseAccess.getConn();
				String hql="update userdata set  stepid=?,data=? where uid=? and spid=?";
				pstmt = cn.prepareStatement(hql);
				pstmt.setInt(1, stepid) ;
				pstmt.setString(2, data) ;
				pstmt.setInt(3, uid) ;
				pstmt.setInt(4, pid) ;
				
			    pstmt.execute();
				temp=true;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			temp=false;
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					temp=false;

			}
		}
		return temp;
	}

	public User getXieyiInfo(int uid, int pid) {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		User user=new User();
		String sql="select data from user where uid=? and spid=?";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, uid) ;
			pstmt.setInt(2, pid) ;
		    rs=pstmt.executeQuery();
			if(rs.next()){
		      user=JsonUtils.parse(rs.getString("data"), User.class) ;
			}
			
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}	
		}
		return user;
	}


	public int[] getStepidbySpid(int spid) {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String sql="select stepid from subprojectstep  where spid =? ";
		int[] stepids  = new int[20] ;
		int[] stepid  = null ;
		int i=0 ;
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, spid) ;
		    rs=pstmt.executeQuery();
			if(rs.next()){
				stepids[i] = rs.getInt("stepid") ;
				i++ ;
			}
			stepid = new int[i] ;
			for(int j=0;j<i;j++){
				stepid[j] = stepids[j] ;
			}
			System.out.println(stepid);
			
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}	
		}
		return stepid ;
	}


	public int getSpidbyOid(int oid) {
		
		int spid=0;
		
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		User user=new User();
		
		String sql="select spid from v_user_subproject_order  where oid =? ";
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, oid) ;
			
		    rs=pstmt.executeQuery();
			if(rs.next()){
				spid = rs.getInt("spid") ;
			}
			
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}	
		}
		return spid;
	}


	

	

}
